﻿package com.rider.model;

import java.util.*;
import java.sql.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import com.carorderdetail.model.CarOrderDetailVO;
import com.member.model.*;

public class CarRiderPathDAO implements CarRiderPathVO_interface {
	private static DataSource ds = null;
	static {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/CARPOOL");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	private static final String INSERT_STMT = "INSERT INTO CAR_RIDER_PATH (Rider_path_id,member_id,distance,Share_fee,start_point,departure_date,end_point,arrival_date,Status,company,pool_type) VALUES ('R'||CAR_RIDER_PATH_seq.NEXTVAL, ?, ?,?,?,?,?,?,?,?,?)";
	private static final String INSERT_FROM_REGULAR = "INSERT INTO CAR_RIDER_PATH (Rider_path_id,member_id,distance,Share_fee,start_point,departure_date,end_point,arrival_date,Status,company,pool_type,regular_id) VALUES ('R'||CAR_RIDER_PATH_seq.NEXTVAL, ?, ?,?,?,?,?,?,?,?,?,?)";
	private static final String GET_ALL_STMT = "SELECT Rider_path_id,member_id,distance,Share_fee,start_point,departure_date,end_point,arrival_date,Status,company,pool_type,regular_id  FROM CAR_RIDER_PATH ";
	private static final String GET_ALL_BY_MEMID_STMT = 
								"SELECT Rider_path_id,member_id,distance,Share_fee,start_point,departure_date,end_point,arrival_date,Status,company,pool_type,regular_id  FROM CAR_RIDER_PATH where member_id = ?";
	private static final String GET_ONE_STMT = 
			"SELECT rider_path_id,member_id,distance,Share_fee,start_point,departure_date,end_point,arrival_date,Status,company,pool_type,regular_id FROM CAR_RIDER_PATH where rider_path_id = ? and Status=0 ";
	private static final String DELETE = "DELETE FROM CAR_RIDER_PATH where rider_path_id = ?";
	private static final String UPDATE = "UPDATE CAR_RIDER_PATH set distance=?, Share_fee=?, start_point=?, departure_date=?, end_point=?, arrival_date=?, Status=?,company=?, pool_type=?,member_id=?,regular_id=? where Rider_path_id = ?";

	//for android 
	
	public List<CarRiderPathVO> findByPrimaryKeyand(List<CarOrderDetailVO> orderdetaillist) {
		List<CarRiderPathVO> list = new ArrayList<CarRiderPathVO>();
		CarRiderPathVO carriderpathVO = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			
			con = ds.getConnection();
			pstmt = con.prepareStatement(GET_ONE_STMT);
			for (CarOrderDetailVO orderlist:orderdetaillist){
				String rider_path_id=orderlist.getRiderPathId();
			pstmt.setString(1, rider_path_id);
			
			rs = pstmt.executeQuery();
       
			while (rs.next()) {
				carriderpathVO = new CarRiderPathVO();
				carriderpathVO.setMember_id(rs.getString("member_id"));
				carriderpathVO.setDistance(rs.getInt("distance"));
				carriderpathVO.setShare_fee(orderlist.getFee());
				carriderpathVO.setStart_point(rs.getString("start_point"));
				carriderpathVO.setDeparture_date(rs.getTimestamp("DEPARTURE_DATE"));
				carriderpathVO.setEnd_point(rs.getString("end_point"));
				carriderpathVO.setArrival_date(rs.getTimestamp("ARRIVAL_DATE"));
				carriderpathVO.setStatus(rs.getInt("status"));
				carriderpathVO.setCompany(rs.getInt("company"));
				carriderpathVO.setPool_type(rs.getString("pool_type"));
				carriderpathVO.setOrder_id(orderlist.getOrderId());
				list.add(carriderpathVO);
			}
		}
			// Handle any driver errors
		}  catch (SQLException se) {
			throw new RuntimeException("A database error occured. "
					+ se.getMessage());
			// Clean up JDBC resources
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return  list;
	}
	@Override
	public void insert(CarRiderPathVO carRiderPathVO) {

		Connection con = null;
		PreparedStatement pstmt = null;

		try {

			con = ds.getConnection();
			pstmt = con.prepareStatement(INSERT_STMT);

			pstmt.setString(1, carRiderPathVO.getMember_id());
			pstmt.setInt(2, carRiderPathVO.getDistance());
			pstmt.setInt(3, carRiderPathVO.getShare_fee());
			pstmt.setString(4, carRiderPathVO.getStart_point());
			pstmt.setTimestamp(5, carRiderPathVO.getDeparture_date());
			pstmt.setString(6, carRiderPathVO.getEnd_point());
			pstmt.setTimestamp(7, carRiderPathVO.getArrival_date());
			pstmt.setInt(8, carRiderPathVO.getStatus());
			pstmt.setInt(9, carRiderPathVO.getCompany());
			pstmt.setString(10, carRiderPathVO.getPool_type());
			

			pstmt.executeUpdate();

			// Handle any driver errors
		}  catch (SQLException se) {
			throw new RuntimeException("A database error occured. "
					+ se.getMessage());
			// Clean up JDBC resources
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}

	}

	@Override
	public void insertByReugularPath(CarRiderPathVO carRiderPathVO,
			Connection con) {

		PreparedStatement pstmt = null;

		try {

			pstmt = con.prepareStatement(INSERT_FROM_REGULAR);

			pstmt.setString(1, carRiderPathVO.getMember_id());
			pstmt.setInt(2, carRiderPathVO.getDistance());
			pstmt.setInt(3, carRiderPathVO.getShare_fee());
			pstmt.setString(4, carRiderPathVO.getStart_point());
			pstmt.setTimestamp(5, carRiderPathVO.getDeparture_date());
			pstmt.setString(6, carRiderPathVO.getEnd_point());
			pstmt.setTimestamp(7, carRiderPathVO.getArrival_date());
			pstmt.setInt(8, carRiderPathVO.getStatus());
			pstmt.setInt(9, carRiderPathVO.getCompany());
			pstmt.setString(10, carRiderPathVO.getPool_type());
			pstmt.setString(11, carRiderPathVO.getRegular_id());//必定要填，不能做修改
			

			pstmt.executeUpdate();

			// Handle any driver errors
		} catch (SQLException se) {
			throw new RuntimeException("A database error occured. "
					+ se.getMessage());
			// Clean up JDBC resources
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}

			}
		}

	}

	@Override
	public void update(CarRiderPathVO carRiderPathVO) {

		Connection con = null;
		PreparedStatement pstmt = null;

		try {

			con = ds.getConnection();
			pstmt = con.prepareStatement(UPDATE);

			pstmt.setInt(1, carRiderPathVO.getDistance());
			pstmt.setInt(2, carRiderPathVO.getShare_fee());
			pstmt.setString(3, carRiderPathVO.getStart_point());
			pstmt.setTimestamp(4, carRiderPathVO.getDeparture_date());
			pstmt.setString(5, carRiderPathVO.getEnd_point());
			pstmt.setTimestamp(6, carRiderPathVO.getArrival_date());
			pstmt.setInt(7, carRiderPathVO.getStatus());
			pstmt.setInt(8, carRiderPathVO.getCompany());
			pstmt.setString(9, carRiderPathVO.getPool_type());
			pstmt.setString(10, carRiderPathVO.getMember_id());
			pstmt.setString(11, carRiderPathVO.getRegular_id());
			pstmt.setString(12, carRiderPathVO.getRider_path_id());

			pstmt.executeUpdate();

			// Handle any driver errors
		}  catch (SQLException se) {
			throw new RuntimeException("A database error occured. "
					+ se.getMessage());
			// Clean up JDBC resources
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}

	}

	@Override
	public void delete(String rider_path_id) {

		Connection con = null;
		PreparedStatement pstmt = null;

		try {

			con = ds.getConnection();
			pstmt = con.prepareStatement(DELETE);

			pstmt.setString(1, rider_path_id);

			pstmt.executeUpdate();

			// Handle any driver errors
		}  catch (SQLException se) {
			throw new RuntimeException("A database error occured. "
					+ se.getMessage());
			// Clean up JDBC resources
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}

	}
	
	@Override
	public MemberVO getMemberByRiderPathId(String riderPathId) {
		MemberService memberSvc = new MemberService();
		CarRiderPathVO carRiderPathVO = findByPrimaryKey(riderPathId);
		String memberId = carRiderPathVO.getMember_id();
		MemberVO memberVO = memberSvc.getOneMem(memberId);
		
		return memberVO;
	}

	@Override
	public CarRiderPathVO findByPrimaryKey(String rider_path_id) {

		CarRiderPathVO carriderpathVO = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {

			con = ds.getConnection();
			pstmt = con.prepareStatement(GET_ONE_STMT);

			pstmt.setString(1, rider_path_id);

			rs = pstmt.executeQuery();

			while (rs.next()) {
				// empVo 也稱為 Domain objects
				carriderpathVO = new CarRiderPathVO();
				carriderpathVO.setRider_path_id(rs.getString("rider_path_id"));
				carriderpathVO.setMember_id(rs.getString("member_id"));
				carriderpathVO.setDistance(rs.getInt("distance"));
				carriderpathVO.setShare_fee(rs.getInt("share_fee"));
				carriderpathVO.setStart_point(rs.getString("start_point"));
				carriderpathVO.setDeparture_date(rs.getTimestamp("departure_date"));
				carriderpathVO.setEnd_point(rs.getString("end_point"));
				carriderpathVO.setArrival_date(rs.getTimestamp("arrival_date"));
				carriderpathVO.setStatus(rs.getInt("status"));
				carriderpathVO.setCompany(rs.getInt("company"));
				carriderpathVO.setPool_type(rs.getString("pool_type"));
				carriderpathVO.setRegular_id(rs.getString("regular_id"));
			}

			// Handle any driver errors
		} catch (SQLException se) {
			throw new RuntimeException("A database error occured. "
					+ se.getMessage());
			// Clean up JDBC resources
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return carriderpathVO;
	}
	
	@Override
	public List<CarRiderPathVO> getAll(String memberId) {
		List<CarRiderPathVO> list = new ArrayList<CarRiderPathVO>();
		CarRiderPathVO carriderVO = null;

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {

			con = ds.getConnection();
			
			if (memberId != null) {
				pstmt = con.prepareStatement(GET_ALL_BY_MEMID_STMT);
				pstmt.setString(1, memberId);
			} else
				pstmt = con.prepareStatement(GET_ALL_STMT);
			
			rs = pstmt.executeQuery();

			while (rs.next()) {
				CarRiderPathVO carriderpathVO = new CarRiderPathVO();
				carriderpathVO.setRider_path_id(rs.getString("rider_path_id"));
				carriderpathVO.setMember_id(rs.getString("member_id"));
				carriderpathVO.setDistance(rs.getInt("distance"));
				carriderpathVO.setShare_fee(rs.getInt("share_fee"));
				carriderpathVO.setStart_point(rs.getString("start_point"));
				carriderpathVO.setDeparture_date(rs.getTimestamp("departure_date"));
				carriderpathVO.setEnd_point(rs.getString("end_point"));
				carriderpathVO.setArrival_date(rs.getTimestamp("arrival_date"));
				carriderpathVO.setStatus(rs.getInt("status"));
				carriderpathVO.setCompany(rs.getInt("company"));
				carriderpathVO.setPool_type(rs.getString("pool_type"));
				carriderpathVO.setRegular_id(rs.getString("regular_id"));

				list.add(carriderpathVO); // Store the row in the vector
			}

			// Handle any driver errors
		} catch (SQLException se) {
			throw new RuntimeException("A database error occured. "
					+ se.getMessage());
			// Clean up JDBC resources
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return list;
	}

	@Override
	public List<CarRiderPathVO> getAll() {
		List<CarRiderPathVO> list = new ArrayList<CarRiderPathVO>();
		CarRiderPathVO carriderVO = null;

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {

			con = ds.getConnection();
			pstmt = con.prepareStatement(GET_ALL_STMT);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				// empVO 也稱為 Domain objects
				CarRiderPathVO carriderpathVO = new CarRiderPathVO();
				carriderpathVO.setRider_path_id(rs.getString("rider_path_id"));
				carriderpathVO.setMember_id(rs.getString("member_id"));
				carriderpathVO.setDistance(rs.getInt("distance"));
				carriderpathVO.setShare_fee(rs.getInt("share_fee"));
				carriderpathVO.setStart_point(rs.getString("start_point"));
				carriderpathVO.setDeparture_date(rs.getTimestamp("departure_date"));
				carriderpathVO.setEnd_point(rs.getString("end_point"));
				carriderpathVO.setArrival_date(rs.getTimestamp("arrival_date"));
				carriderpathVO.setStatus(rs.getInt("status"));
				carriderpathVO.setCompany(rs.getInt("company"));
				carriderpathVO.setPool_type(rs.getString("pool_type"));
				carriderpathVO.setRegular_id(rs.getString("regular_id"));

				list.add(carriderpathVO); // Store the row in the vector
			}

			// Handle any driver errors
		} catch (SQLException se) {
			throw new RuntimeException("A database error occured. "
					+ se.getMessage());
			// Clean up JDBC resources
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return list;
	}
	
	public static void main(String[] args) {

		CarRiderPathDAO dao = new CarRiderPathDAO();

		// 新增
		// CarRiderPathVO carriderVO1= new CarRiderPathVO();
		// carriderVO1.setRider_path_id("207");
		// carriderVO1.setMember_id("1008");
		// carriderVO1.setDistance(10);
		// carriderVO1.setShare_fee(300);
		// carriderVO1.setStart_point("7");
		// carriderVO1.setDeparture_date(java.sql.Date.valueOf("2012-09-13"));
		// carriderVO1.setEnd_point("9");
		// carriderVO1.setArrival_date(java.sql.Date.valueOf("2012-09-13"));
		// carriderVO1.setStatus(5);
		// carriderVO1.setCompany(1);
		// dao.insert(carriderVO1);
		// System.out.println("OK");

		// 修改
		CarRiderPathVO carriderVO2 = new CarRiderPathVO();
		carriderVO2.setRider_path_id("207");
		carriderVO2.setMember_id("1119");
		carriderVO2.setDistance(10);
		carriderVO2.setShare_fee(300);
		carriderVO2.setStart_point("6");
		carriderVO2.setDeparture_date(java.sql.Timestamp.valueOf("2012-09-13"));
		carriderVO2.setEnd_point("9");
		carriderVO2.setArrival_date(java.sql.Timestamp.valueOf("2012-09-13"));
		carriderVO2.setStatus(5);
		carriderVO2.setCompany(1);
		dao.update(carriderVO2);
		System.out.println("OK");

		// 刪除
		// dao.delete("207");
		// System.out.println("---------------------");
		// 查詢
		// CarRiderPathVO carriderVO3 = dao.findByPrimaryKey("207");
		// System.out.print(carriderVO3.getMember_id() + ",");
		// System.out.print(carriderVO3.getDistance() + ",");
		// System.out.print(carriderVO3.getShare_fee() + ",");
		// System.out.print(carriderVO3.getStart_point() + ",");
		// System.out.print(carriderVO3.getDeparture_date() + ",");
		// System.out.print(carriderVO3.getEnd_point() + ",");
		// System.out.print(carriderVO3.getArrival_date() + ",");
		// System.out.print(carriderVO3.getStatus() + ",");
		// System.out.print(carriderVO3.getMember_id() + ",");
		// System.out.print(carriderVO3.getCompany() + ",");
		// System.out.print(carriderVO3.getPool_type() + ",");
		// System.out.println("---------------------");

		// 查詢

		// System.out.println("---------------------");
		// List<CarRiderPathVO> list = dao.getAll();
		// for (CarRiderPathVO acardriver : list) {
		// System.out.println("---------------------");
		// System.out.print(acardriver.getRider_path_id() + ",");
		// System.out.print(acardriver.getMember_id() + ",");
		// System.out.print(acardriver.getDistance() + ",");
		// System.out.print(acardriver.getShare_fee() + ",");
		// System.out.print(acardriver.getStart_point() + ",");
		// System.out.print(acardriver.getDeparture_date() + ",");
		// System.out.print(acardriver.getEnd_point() + ",");
		// System.out.print(acardriver.getArrival_date() + ",");
		// System.out.print(acardriver.getStatus() + ",");
		// System.out.print(acardriver.getCompany() + ",");
		// System.out.print(acardriver.getPool_type() + ",");
		// System.out.println("---------------------");
		// }
	}

}